package ADBI;

$VERSION = 1.0;
use base 'ADBI::DBClass';

#my $debug = 0;

sub new
{
	my $class = shift;
	my $parm = shift;
	my $self = ADBI::DBClass->new($parm);
	bless $self,$class;
	return $self;
}

sub table
{
	my $self = shift;
	@_	?	$self->{'table'} = shift
		:	$self->{'table'};
}

sub get_all
{
	my $self = shift;
	my $parm = shift || ''; 

	my $limit = ($parm =~ m/\S/)
				? "limit $parm"
				: "";
				
	my $sql_cmd = qq|select *|
				. qq| from $self->{'table'} $limit|;
	print $sql_cmd if exists $self->{'debug'};
	my $sth = $self->{'dbh'}->prepare($sql_cmd);
	$sth->execute();
	my @result;
	while (my $ref = $sth->fetchrow_arrayref)
	{
		my @a = @$ref;
		push @result,\@a;
	}
	return \@result;
}

sub groupby
{
	my $self = shift;
	my $pre_time = shift;
	my $current_time = shift;
	my $domain = shift;
	my $column = shift;
	
	$column = join(",",@{ $column }) if ref $column;
	#print $columns;

	my $SQL = qq|select | .
					join(",",$column,"count(*)") .
					qq| from $self->{'table'}
					where time between ? and ?
					and hostname like ?
					group by $column
				|;
	print $SQL if exists $self->{'debug'};
	my $sth = $self->{'dbh'}->prepare($SQL);
	eval {
		$sth->execute("$pre_time","$current_time","%" . $domain);
	};
	return "$@" if $@;
	
	my $result = {};
	while (my $ref = $sth->fetchrow_arrayref)
	{
		$result->{$ref->[0]} = $ref->[1];
	}
	return $result;
}

sub domain_request
{
	my $self = shift;
	my $arg = shift;
	
	my $SQL = qq|select count(*)
				from $self->{'table'}
				where time between ? and ?
					and hostname like ?
				|;
				
	$SQL .= " and req_time >= 1 " if exists $arg->{'type'} && $arg->{'type'} eq 'slow';
	
	print $SQL,"\n" if exists $self->{'debug'};
	
	my $domain = "%" . $arg->{'domain'};
	
	my $sth = $self->{'dbh'}->prepare($SQL);
	eval {
		$sth->execute($arg->{'pre_time'}, $arg->{'next_time'}, $domain);
	};
	print "$@\n" if ($@);
	my $result;
	while (my $ref = $sth->fetchrow_arrayref)
	{
		$result = @$ref[0];
	}
	return $result;
}

sub domain_request_GroupByUpStreamHost
{
	my $self = shift;
	my $arg = shift;	
	
	my $SQLCase = "";
			
	$SQLCase = " and req_time >= 1 " if exists $arg->{'type'} && $arg->{'type'} eq 'slow';
	
	my $SQL = qq|select * from (select upstream_host as hostname, count(*) as c from $self->{'table'} 
					where time between ? and ? 
					and hostname like ? $SQLCase group by upstream_host) as t order by c desc;	
				|;
	
	print $SQL,"\n" if exists $self->{'debug'};
	
	my $domain = "%" . $arg->{'domain'};
	
	my $sth = $self->{'dbh'}->prepare($SQL);
	eval {
		$sth->execute($arg->{'pre_time'}, $arg->{'next_time'}, $domain);
	};
	print "$@\n" if ($@);	
	my $c = 0;
        my $server_name = "";
        my $text_stream = "";
	
	while (my $ref = $sth->fetchrow_arrayref)
	{
		$c = $ref->{'c'};
        $server_name = $ref->{'hostname'};
        $text_stream .= $server_name . " : " . $c . "\n";
	}
	return $text_stream;
}

sub preformance_result
{
	my $self = shift;
	my $old_time = shift;
	my $current_time = shift;
	my $url = shift;
	
    my $SQL = qq|
        select upstream_host,count(*),avg(upstream_resp),avg(bytes_sent)
        from $self->{'table'}
        where time between ? and ?
        and url ~ ?
        and code = '200'
        group by upstream_host;
        |;
				
	my $sth = $self->{'dbh'}->prepare($SQL);
	$sth->execute("$old_time", "$current_time", "$url");
	my %upstream;
	while (my $ref = $sth->fetchrow_arrayref)
	{
        next if $ref->[0] eq '-';
        #print "@$ref\n";
        $ref->[0] =~ s/\s+$//;
        $ref->[2] *= 1000;
        push @{ $upstream{$ref->[0]} },  $url,$ref->[1], $ref->[2],$ref->[3];
	}
	return \%upstream;
}

sub analysis
{
	my $self = shift;
	my $pre_time = shift;
	my $current_time = shift;
	my $upstream_host = shift;
	my $url_types = shift;
	my $total = shift;
	
	
	my $percent_90 = int ($total * 0.1);
	
    
	my $SQL = qq|
			select upstream_resp from $self->{'table'}
			where time between ? and ?
			and upstream_host = ?
            and url ~ ?
            and code = '200'
            order by upstream_resp desc
	|;
	
	my $sth = $self->{'dbh'}->prepare($SQL);
	$sth->execute( $pre_time, $current_time,  $upstream_host, "$url_types" );
	my @mode_time;
	my %performance_value;
	while (my $ref = $sth->fetchrow_arrayref)
	{
		push @mode_time,  $ref->[0] *= 1000;
		my $i = int $ref->[0] / 10;
		$performance_value{$i} += 1;
	}
	my $result;
	foreach my $key (sort {$performance_value{$b} <=> $performance_value{$a}}keys %performance_value)
	{
		$result = $key * 10;
		last;
	}
	return ( $mode_time[$percent_90 - 1], $result);
}

sub countip
{
	my $self = shift;
	my $pre_time = shift;
	my $current_time = shift;
	my $num = shift || 100;
	
	my $SQL = qq{
		select ip,count(*)
		from $self->{'table'}
		where time between ? and ?
		group by ip
		having count(*) > ?
	};
	my $sth = $self->{'dbh'}->prepare($SQL);
	$sth->execute("$pre_time", "$current_time", "$num");
	my $result;
	while (my $ref = $sth->fetchrow_arrayref)
	{
		$ref->[0] =~ s/\s+$//;
		$result->{$ref->[0]} = $ref->[1];
	}
	return $result;
}

sub _createtable
{
	my $self = shift;
    my $tablename = shift;
    my $SQL	=  qq{
                create table $tablename (
                id serial primary key,
                req_time NUMERIC(7,3),
                upstream_resp NUMERIC(7,3),
                upstream_host char(15),
                time time without time zone not null,
                ip char(15) not null,
                hostname varchar(255),
                url varchar(255) not null,
                code int,
                bytes_sent int,
                referer varchar(255),
                useragent varchar(255)
        	)
    	};;
    my $sth = $self->{'dbh'}->prepare($SQL);
    $sth->execute() or die $self->{'dbh'}->errstr;
}

sub _droptable
{
	my $self = shift;
    my $tablename = shift;
    my $SQL       = qq{
        drop table $tablename
    };
    my $sth = $self->{'dbh'}->do($SQL) or die $self->{'dbh'}->errstr;
}

1;
